<?php

namespace console\components\db;

use Yii;
use yii\db\Schema;
use yii\db\Exception;
use yii\db\ColumnSchemaBuilder;
use yii\db\Query;
use yii\helpers\ArrayHelper;

/**
 * Migration自定义扩展
 * 
 * @author emhome <emhome@163.com>
 * @since 1.0
 */
class Migration extends \yii\db\Migration {

    const ENGINE_INNODB = 'InnoDB';
    const ENGINE_MYISAM = 'MyISAM';

    /**
     * @var array 忽略创建的表
     */
    public $ignoreTables = [];

    /**
     * @var string 创建表选项
     */
    public $tableOptions = null;

    /**
     * @var array 移除时表数组
     */
    public $dropTables = [];

    /**
     * @var string 默认使用事务性表存储引擎
     */
    public $tableEngine = self::ENGINE_INNODB;

    /**
     * @var boolean 是否增加备注信息
     */
    public $useComment = YII_DEBUG;

    /**
     * @inheritdoc
     */
    public function init() {
        parent::init();
        if ($this->db->driverName === 'mysql') {
            $this->tableOptions = 'CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ENGINE=' . $this->tableEngine;
        }
        if (isset(Yii::$app->params['migrate.ignore.tables']) && !empty(Yii::$app->params['migrate.ignore.tables'])) {
            $this->ignoreTables = ArrayHelper::merge($this->ignoreTables, Yii::$app->params['migrate.ignore.tables']);
        }
    }

    /**
     * @inheritdoc
     */
    public function createTable($table, $columns, $options = null) {
        if ($this->checkTableIgnored($table)) {
            return false;
        }
        $this->dropTables[] = $table;
        if ($options === null) {
            $options = $this->tableOptions;
        }
        if ($this->useComment === false) {
            foreach ($columns as $type) {
                /* @var $type \yii\db\ColumnSchemaBuilder */
                if ($type instanceof ColumnSchemaBuilder && $type->comment !== null) {
                    $type->comment = null;
                }
            }
        }
        parent::createTable($table, $columns, $options);
    }

    /**
     * @inheritdoc
     */
    public function dropTable($table, $subsize = 0) {
        $sql = "DROP TABLE IF EXISTS $table;";
        if ($subsize > 0) {
            $pos = strpos($table, '}}');
            if ($pos !== false) {
                $table = substr_replace($table, '_', $pos, 2);
            }
            for ($i = 0; $i < $subsize; $i++) {
                $_table = $table . $i . '}}';
                $sql .= "DROP TABLE IF EXISTS $_table;";
            }
        }
        $time = $this->beginCommand($sql);
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * @inheritdoc
     */
    public function dropView($view) {
        $sql = "DROP VIEW IF EXISTS `$view`";
        $time = $this->beginCommand($sql);
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * @inheritdoc
     */
    public function dropProcedure($procedure) {
        $sql = "DROP PROCEDURE IF EXISTS `$procedure`";
        $time = $this->beginCommand($sql);
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * 移除表名中的通配符
     * @param string $name
     */
    protected function basenameTable($name) {
        return str_replace(['{', '}', '%'], '', $name);
    }

    /**
     * 外键格式构建器
     * @param string $delete
     * @param string $update
     */
    protected function buildFkClause($delete = '', $update = '') {
        return implode(' ', ['', $delete, $update]);
    }

    /**
     * 检测是否忽略的表
     * @param string $tablename
     */
    protected function checkTableIgnored($tablename) {
        $table = $this->basenameTable($tablename);
        if (in_array($table, $this->ignoreTables)) {
            return true;
        }
        return false;
    }

    /**
     * 检测表是否存在
     * @param string $tablename
     */
    protected function isExistsTable($tablename) {
        $table = $this->basenameTable($tablename);
        $db = $this->db;
        $tableSchemas = $db->getSchema()->getTableNames();
        if (in_array($db->tablePrefix . $table, $tableSchemas)) {
            return true;
        }
        return false;
    }

    /**
     * 运行数据表构建
     * @param string $tableName
     * @param array $columns
     * @param array $primaryKeys
     * @param array $indexs
     * @param boolean $comment
     */
    protected function executeTableBuild($tableName, $columns, $primaryKeys = [], $indexs = [], $comment = false) {
        if (strpos($tableName, '{{') === false) {
            $tableName = '{{%' . $tableName . '}}';
        }
        $created = $this->createTable($tableName, $columns);
        if ($created === false) {
            return null;
        }
        if (!empty($primaryKeys)) {
            foreach ($primaryKeys as $_key) {
                $this->addPrimaryKey($_key, $tableName, $_key);
            }
        }
        if ($this->useComment !== false && $comment) {
            $this->addCommentOnTable($tableName, $comment);
        }
        if (!empty($indexs)) {
            foreach ($indexs as $index) {
                if (!isset($index['columns']) || empty($index['columns'])) {
                    continue;
                }
                $indexColumns = $index['columns'];
                $defaultIndexName = 'idx_' . $this->basenameTable($tableName) . '_';
                if (is_string($indexColumns)) {
                    $defaultIndexName .= $indexColumns;
                } elseif (is_array($indexColumns)) {
                    $defaultIndexName .= implode('_', $indexColumns);
                }
                $indexName = ArrayHelper::remove($index, 'name', $defaultIndexName);
                $unique = ArrayHelper::remove($index, 'unique', false);
                $this->createIndex($indexName, $tableName, $indexColumns, $unique);
            }
        }
    }

    /**
     * 表结构构建器
     * @param array $table
     */
    public function tableBuilder($table) {
        if (!is_array($table) || empty($table)) {
            throw new Exception('This table struction is not array or empty!');
        }
        if (!isset($table['name']) || !is_string($table['name'])) {
            throw new Exception('This table name is not exists or string!');
        }
        if (!isset($table['columns']) || empty($table['columns'])) {
            throw new Exception('This table columns is not exists or empty!');
        }

        $tableName = $table['name'];
        $columns = $table['columns'];

        $primaryKeys = ArrayHelper::remove($table, 'key', []);
        $indexs = ArrayHelper::remove($table, 'index', []);
        $comment = ArrayHelper::remove($table, 'comment', false);
        $subsize = ArrayHelper::remove($table, 'subsize', false);
        if ($subsize !== false) {
            for ($i = 0; $i < $subsize; $i++) {
                $this->executeTableBuild($tableName . '_' . $i, $columns, $primaryKeys, $indexs, $comment);
            }
        } else {
            $this->executeTableBuild($tableName, $columns, $primaryKeys, $indexs, $comment);
        }
    }

    public function viewBuilder($view) {
        if (!is_array($view) || empty($view)) {
            throw new Exception('This view struction is not array or empty!');
        }
        if (!isset($view['name']) || !is_string($view['name'])) {
            throw new Exception("This view's name not exists or datatype is not string!");
        }
        if (!isset($view['tables']) || empty($view['tables'])) {
            throw new Exception('This view tables is not exists or empty!');
        }

        $query = new Query();
        $viewName = $view['name'];
        $tables = $view['tables'];
        $where = $view['where'];

        $selects = [];
        $from = [];
        foreach ($tables as $table => $columns) {
            $from[] = $table;
            foreach ($columns as $alias => $col) {
                $_field = "$table.`$col`";
                if (is_string($alias)) {
                    $_field .= ' as `' . $alias . '`';
                }
                $selects[] = $_field;
            }
        }
        $query->select($selects)->from($from)->where($where);

        $time = $this->beginCommand("create view $viewName");
        $this->db->createCommand()->createView($viewName, $query)->execute();
        $this->endCommand($time);
    }

    public function procedureBuilder($procedure) {
        if (!is_array($procedure) || empty($procedure)) {
            throw new Exception('This procedure struction is not array or empty!');
        }
        if (!isset($procedure['name']) || !is_string($procedure['name'])) {
            throw new Exception("This procedure's name not exists or datatype is not string!");
        }
        if (!isset($procedure['sql']) || !is_string($procedure['sql']) || empty($procedure['sql'])) {
            throw new Exception("This procedure's sql not exists or datatype is not string!");
        }
        $procedureName = $procedure['name'];
        $sql = "CREATE PROCEDURE `$procedureName`";
        $params = [];
        if (isset($procedure['params']) && !empty($procedure['params'])) {
            foreach ($procedure['params'] as $param) {
                if (!is_array($param)) {
                    continue;
                }
                if (!isset($param['name']) || empty($param['name'])) {
                    continue;
                }
                $temp = [];
                $mode = isset($param['mode']) ? strtoupper($param['mode']) : '';
                if (in_array($mode, ['IN', 'OUT', 'INOUT'])) {
                    $temp[] = $mode;
                }
                $temp[] = '`' . $param['name'] . '`';
                $type = isset($param['type']) ? $param['type'] : '';
                if ($type) {
                    $size = isset($param['size']) ? intval($param['size']) : 0;
                    if ($size) {
                        $type .= '(' . $size . ')';
                    }
                    $temp[] = $type;
                }
                $params[] = implode(' ', $temp);
            }
        }
        if (!empty($params)) {
            $sql .= '(' . implode(',', $params) . ')';
        } else {
            $sql .= '()';
        }
        $sql .= "BEGIN\n";
        $sql .= (string) $procedure['sql'];
        $sql .= "\nEND;";

        $time = $this->beginCommand("create procedure $procedureName");
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * 批量sql插入数据
     * @param string $tableName
     * @param array $columns
     * @param string $path
     */
    public function sqlBatchInsert($tableName, $columns, $path) {
        $this->truncateTable('{{%' . $tableName . '}}');
        $string = file_get_contents($path);
        $format = 'INSERT INTO `%s` (`' . implode('`, `', $columns) . '`) VALUES';
        $delimiter = sprintf($format, $tableName);
        $insertSchema = sprintf($format, $this->db->tablePrefix . $tableName);
        $data = explode($delimiter, $string);
        foreach ($data as $_sql) {
            if ($_sql) {
                $this->execute($insertSchema . $_sql);
            }
        }
    }

    /**
     * Creates a tinyint column.
     * @param int $length column size definition i.e. the maximum string length.
     * This parameter will be ignored if not supported by the DBMS.
     * @return ColumnSchemaBuilder the column instance which can be further customized.
     * @since 2.0.8
     */
    public function tinyint($length = null) {
        if ($length != null) {
            return $this->getDb()->getSchema()->createColumnSchemaBuilder('tinyint', $length);
        }
        return $this->getDb()->getSchema()->createColumnSchemaBuilder(\yii\db\Schema::TYPE_BOOLEAN);
    }

    /**
     * Creates a longtext column.
     * @return ColumnSchemaBuilder the column instance which can be further customized.
     * @since 2.0.8
     */
    public function longtext() {
        return $this->getDb()->getSchema()->createColumnSchemaBuilder('longtext');
    }

    /**
     * Creates a enum column.
     * @param array $items column data definition.
     * @return ColumnSchemaBuilder the column instance which can be further customized.
     * @since 2.0.8
     */
    public function enum($items = []) {
        return $this->getDb()->getSchema()->createColumnSchemaBuilder("enum('" . implode("','", $items) . "')");
    }

    /**
     * Creates a unix timestamp column.
     * @param int $length column size definition i.e. the maximum string length.
     * @return ColumnSchemaBuilder the column instance which can be further customized.
     * @since 2.0.8
     */
    public function unixTimestamp($length = 10) {
        return $this->getDb()->getSchema()->createColumnSchemaBuilder(Schema::TYPE_INTEGER, $length)->unsigned()->notNull()->defaultValue(0);
    }

    /**
     * Creates a geometry column.
     * @return ColumnSchemaBuilder the column instance which can be further customized.
     * @since 2.0.8
     */
    public function geometry() {
        return $this->getDb()->getSchema()->createColumnSchemaBuilder('geometry');
    }

    /**
     * Creates a geometryHash column.
     * @param int $length column size definition i.e. the maximum string length.
     * @return ColumnSchemaBuilder the column instance which can be further customized.
     * @since 2.0.8
     */
    public function geometryHash($from, $size = 8, $length = 20) {
        $sql = "GENERATED ALWAYS AS (st_geohash(`$from`, $size)) VIRTUAL";
        return $this->getDb()->getSchema()->createColumnSchemaBuilder(Schema::TYPE_STRING, $length)->append($sql);
    }

}
